package online.sh.cn.fy.common.utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;



public class SqlUtil {
	private  Connection conn;  
    private  PreparedStatement  st; 
    private  ResultSet rs;
    
    public static String getSystime(){
    	String time = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(Calendar.getInstance().getTime());
    	return time;
    } 
    
    public static String getSystimeSign(){
    	String time = new SimpleDateFormat("yyyy-MM-dd").format(Calendar.getInstance().getTime());
    	return time;
    } 
    
    //查询楼长表全部大学
    public List<String> getfyCaptionshnamefromOpenId(String OpenId){
    	List<String> result=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct school  from fyuser where openid =?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1,OpenId );
            rs = st.executeQuery();
        	result=new ArrayList<String>();
            while (rs.next()) {
            	result.add(rs.getString("school"));
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("error："+e.getMessage());
            return null;
        }
    }
    
    //查询楼长表全部大学
    public List<String> getfyCaptionshname(){
    	List<String> result=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct shname  from fycaptian";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
        	result=new ArrayList<String>();
            while (rs.next()) {
            	result.add(rs.getString("shname"));
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长表全部大学失败："+e.getMessage());
            return null;
        }
    }
    //根据大学查询校区
    public List<String> getfyCaptionarea(String shname){
    	List<String> result=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct sharea  from fycaptian where shname=?";  
            st = (PreparedStatement) conn.prepareStatement(sql); 
            st.setString(1,shname );
            rs = st.executeQuery();
        	result=new ArrayList<String>();
            while (rs.next()) {
            	result.add(rs.getString("sharea"));
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长表全部大学失败："+e.getMessage());
            return null;
        }
    }
    //根据校区查询宿舍
    public List<String> getfyCaptionbuilding(String sharea){
    	List<String> result=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct bdname  from fycaptian where sharea=?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1,sharea );
            rs = st.executeQuery();
        	result=new ArrayList<String>();
            while (rs.next()) {
            	result.add(rs.getString("bdname"));
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长表全部大学失败："+e.getMessage());
            return null;
        }
    }
    //根据学校查询宿舍
    public List<String> getfyCaptionbuilding1(String shname){
    	List<String> result=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct bdname  from fycaptian where shname=?";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            st.setString(1,shname );
            rs = st.executeQuery();
        	result=new ArrayList<String>();
            while (rs.next()) {
            	result.add(rs.getString("bdname"));
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长表全部失败："+e.getMessage());
            return null;
        }
    }
    //根据学校校区宿舍楼查询
    public Map<String,String> getfyCaption(String school,String area,String building){
    	Map<String,String> result=new HashMap<String,String>();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct cpname,cplevel,cpqq,cpmobile,cpwechat  from fycaptian where sharea=? and shname=? and bdname=?";  

			if(area.equals("无")){
	            sql = "select distinct cpname,cplevel,cpqq,cpmobile,cpwechat  from fycaptian where shname=? and bdname=?";  
	            st = (PreparedStatement) conn.prepareStatement(sql);  
	            st.setString(1,school);
	            st.setString(2,building );
			}else{
				st = (PreparedStatement) conn.prepareStatement(sql);  
	            st.setString(1,area );
	            st.setString(2,school );
	            st.setString(3,building );
			}
           
            rs = st.executeQuery();
            while (rs.next()) {
            	result.put("cpname", rs.getString("cpname").trim());
            	result.put("cplevel", rs.getString("cplevel").trim());
            	result.put("cpqq", rs.getString("cpqq").trim());
            	result.put("cpmobile", rs.getString("cpmobile").trim());
            	result.put("cpwechat", rs.getString("cpwechat").trim());
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长失败："+e.getMessage());
            return null;
        }
    }
    
    //根据学校校区宿舍楼查询
    public Map<String,String> getfyCaption1(String school){
    	Map<String,String> result=new HashMap<String,String>();
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select distinct cpname,cplevel,cpqq,cpmobile,cpwechat  from fycaptian where shname=?";  
	        st = (PreparedStatement) conn.prepareStatement(sql);  
	        st.setString(1,school);
            rs = st.executeQuery();
            while (rs.next()) {
            	result.put("cpname", rs.getString("cpname").trim());
            	result.put("cplevel", rs.getString("cplevel").trim());
            	result.put("cpqq", rs.getString("cpqq").trim());
            	result.put("cpmobile", rs.getString("cpmobile").trim());
            	result.put("cpwechat", rs.getString("cpwechat").trim());
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长失败："+e.getMessage());
            return null;
        }
    }
    
  //取出所有搜索关键字
    public List<Map> getfyHelp(){
    	List<Map> result=null;
    	Map<String,String> result1=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from fyhelp where level !=0 order by LENGTH(help) desc";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            result=new ArrayList<Map>();
            while (rs.next()) {
            	result1=new HashMap<String,String>();
            	result1.put("number", rs.getString("number").trim());
            	result1.put("help", rs.getString("help").trim());
            	result1.put("level", rs.getInt("level") + "");
            	result.add(result1);
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("取出所有搜索关键字失败："+e.getMessage());
            return null;
        }
    }
    
    //根据上级编号取得下级编号内容
    public List<Map> getfyhelpKeys(String number){
    	List<Map> result=null;
    	Map<String,String> result1=null;

    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
			//select * from fyhelp where number REGEXP '^T1.1.' and LENGTH(number) = LENGTH('T1.1.')+1
            String sql = "select * from fyhelp where number REGEXP '^" + number +".' and LENGTH(number) = LENGTH('" + number +".')+1";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            result=new ArrayList<Map>();

            while (rs.next()) {
            	result1=new HashMap<String,String>();
            	result1.put("number", rs.getString("number").trim());
            	result1.put("help", rs.getString("help").trim());
            	result1.put("level", rs.getInt("level") + "");
            	result.add(result1);
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长失败："+e.getMessage());
            return null;
        }
    }
    
    
    
    //根据编号取得内容
    public List<Map> getfyhelp(String number){
    	List<Map> result=null;
    	Map<String,String> result1=null;
    	JDBCUtil ju=new JDBCUtil();
		conn=ju.connect();
		try {  
            String sql = "select * from fyhelp where number like '" + number +"%' and level=0";  
            st = (PreparedStatement) conn.prepareStatement(sql);  
            rs = st.executeQuery();
            result=new ArrayList<Map>();
            while (rs.next()) {
            	result1=new HashMap<String,String>();
            	result1.put("number", rs.getString("number").trim());
            	result1.put("help", rs.getString("help").trim());
            	result1.put("level", rs.getInt("level") + "");
            	result.add(result1);
            }
            conn.close();
            st.close();
            rs.close();
            return result;
        } catch (SQLException e) {  
            System.out.println("查询楼长失败："+e.getMessage());
            return null;
        }
    }
    
    public static void main(String[] args) {
		System.out.println(getSystime());
	}
}
